# -*- coding: utf-8 -*-

from WindPy import *
import numpy as np
import pandas as pd
from datetime import *
import matplotlib.pyplot as plt
import os.path
import xlwt
import xlrd
#from sqlalchemy import create_engine,text
#from sqlalchemy.orm import sessionmaker
os.chdir("C:\\PA80") 

#计算涨跌幅序列，夏普比率，最大回撤，波动率
def net_index(list1):
    drawdown=0
    for i in range(len(list1)):
        a=0
        for j in range(0,i+1):
            if a<list1[j]:
                a=list1[j]
        if drawdown<1-list1[j]/a:
            drawdown=1-list1[j]/a
    yie=[]
    for i in range(len(list1)-1):
        yie.append(np.log(list1[i+1]/list1[i]))
    sharp_ratio=(np.sum(yie)/len(yie)*250-0.03)/((np.std(pd.Series(yie))*np.sqrt(250)))
    std=np.std(pd.Series(yie))*np.sqrt(250)
    return drawdown,sharp_ratio,std
    
#对字典排序，按值从大到小排序，d[1]为按照value排序    
def rank_reverse(dict1):
    list1=sorted(dict1.items(),key=lambda d:d[1],reverse=True)
    dict2={}
    for i in range(len(list1)):
        dict2[list1[i][0]]=i+1
    return dict2
    
#对字典排序，按值从小到大排序，默认
def rank_order(dict1):
    list1=sorted(dict1.items(),key=lambda d:d[1],reverse=False)
    dict2={}
    for i in range(len(list1)):
        dict2[list1[i][0]]=i+1
    return dict2

def Normal_ic_Ratio(dict1,dict2):
    for i in range(len(dict1)):
        if i == 0:
            ic_ratio = 0
        else:
            ic_ratio = np.corrcoef(dict1[i-1][0],dict2[i][0])
    return ic_ratio        
    
    
w.start()
start='20150101'
stop='20151231'

#调仓频率
frequency=20
#区间所有交易日
trade=w.tdays(start,stop).Data[0]

#调仓日期
i=0
change=[]
for day in trade:
    if (i%frequency==0):
        change.append(trade[i])
    i+=1

#选股回测

code_list_set=[]

r1=0.3; r2=0.2; r3=0.25; r4=0.153; r5=0.1; r6=0;  r7=0;  r8=0     # 6因子权重
#r1=0.0084021; r2=0.402558;  r3=0.145843889;  r4=0.207200765;  r5=0.084902392; r6=0.06276645; r7=0.0; r8=0.088326422   #7因子权重
#r1=0.006222643; r2=0.298136084;  r3=0.108012584;  r4=0.153453739;  r5=0.062879061; r6=0.046485091; r7=0.259395888; r8=0.065414911  #8因子权重
top=80; 
select=[];   
index = '000300.SH'  
for date in change:
    i= change.index(date)
    print ('selecting ',date.strftime("%Y-%m-%d"))
    code_list=w.wset("sectorconstituent","date="+date.strftime("%Y-%m-%d")+";sectorid=a001010100000000;field=wind_code").Data[0]
#   code_list=w.wset("sectorconstituent","date="+date.strftime("%Y-%m-%d")+";windcode="+index+";field=wind_code").Data[0]
    west_eps=w.wsd(code_list, "west_eps_FTM", date, date, "Fill=Previous;PriceAdj=F").Data[0]  #一致预测eps 未来12个月
    eps_now=w.wsd(code_list, "eps_ttm", date, date, "Fill=Previous;PriceAdj=F").Data[0]  #当前eps 
    west_pe=w.wsd(code_list, "pe_est_ftm", date, date, "Fill=Previous;PriceAdj=F").Data[0]  #预测pe, 未来12个月
    roe=w.wsd(code_list, "roe_ttm",date, date, "Fill=Previous;PriceAdj=F").Data[0]  #历史roe  净资产收益率
    profit=w.wsd(code_list, "op_ttm", date, date, "unit=1;Fill=Previous;PriceAdj=F").Data[0]  #营业利润
    revenue=w.wsd(code_list, "or_ttm", date, date, "unit=1;Fill=Previous;PriceAdj=F").Data[0]  #营业收入
    brate=w.wsd(code_list, "div_aualaccmdivpershare", date, date, 'year='+str(date.year)+';Fill=Previous;PriceAdj=F').Data[0]  #年度累计单位分红
    bps = w.wsd(code_list, "bps_new", date, date, "Fill=Previous;PriceAdj=F").Data[0]
    roic=w.wsd(code_list,'roa_ttm',date,date,'Fill=Previous;PriceAdj=F').Data[0]  #roic因子
    pb=w.wsd(code_list, "pb_lf", date, date, "Fill=Previous;PriceAdj=F").Data[0]  #pb因子
    ipo_date = w.wsd(code_list, "ipo_date", date, date, "Fill=Previous").Data[0]
    trade_status=w.wsd(code_list, "trade_status", date, date).Data[0]     # 读取交易状态 
    f1={}; f2={}; f3={}; f4={}; f5={};f6={};f7={};f8={}; trade_s={}; ipo_d ={}
    for code in code_list:
        j = code_list.index(code)
        if trade_status[j] is None:
            trade_s[code]=10000
        elif trade_status[j].encode('utf-8')==u'交易'.encode('utf-8'):
            trade_s[code]=0     #0为正常交易
        else:
            trade_s[code]=10000
        if ipo_date[j] is None or ipo_date[j]!=ipo_date[j] or type(ipo_date[j])==type('123'):
            ipo_d[code]=10000
        elif  (date.year - ipo_date[j].year) <= 2:
            ipo_d[code]=10000
        else:
            ipo_d[code]=0
        if west_eps[j] is None or west_eps[j]!=west_eps[j] or type(west_eps[j])==type('123') or eps_now[j]!=eps_now[j] or type(eps_now[j])==type('123') or eps_now[j] is None or eps_now[j]<0.00000001:
            f1[code]=-10000.0
        else:
            f1[code]=west_eps[j]/eps_now[j]-1   #EPSGrw因子，越大收益越大
        if roe[j] is None or roe[j]!=roe[j] or type(roe[j])==type('123'):
            f2[code] = -10000.0
        else:
            f2[code] = roe[j]     #roe， 越大收益越大
        if west_pe[j] is None or west_pe[j]!=west_pe[j] or type(west_pe[j])==type('123') or eps_now[j] is None or west_eps[j]<0.000000001:
            f3[code] = -10000.0   
        else:
            f3[code] = 1/west_pe[j]     #forword ep 越大收益越大
        if profit[j] is None or profit[j]!=profit[j] or type(profit[j])==type('123') or revenue[j] is None or revenue[j]!=revenue[j] or type(revenue[j])==type('123') or revenue[j]<1.0:
            f4[code]=-10000.0
        else:
            f4[code]=profit[j]/revenue[j]   #neopr因子，越大收益越大
        if brate[j] is None or brate[j]!=brate[j] or type(brate[j])==type('123'):
            f5[code]=-10000.0
        else:
            f5[code]=brate[j]  #PR因子，越大收益越大
        if bps[j] is None or bps[j]!=bps[j] or type(bps[j])==type('123'):
            f6[code]=-10000.0
        else:
            f6[code]=bps[j]  #BPS因子，越大收益越大
        if roic[j] is None or roic[j]!=roic[j] or type(roic[j])==type('123'):
            f7[code]=-10000.0
        else:
            f7[code]=roic[j]  #roic因子
        if pb[j] is None or pb[j]!=pb[j] or type(pb[j])==type('123') or pb[j]<0:
            f8[code]=10000.0
        else:
            f8[code]=pb[j]   #pb因子，越小收益越大     
    rank1=rank_reverse(f1)
    rank2=rank_reverse(f2)
    rank3=rank_reverse(f3)
    rank4=rank_reverse(f4)
    rank5=rank_reverse(f5)
    rank6=rank_reverse(f6)
    rank7=rank_reverse(f7)
    rank8=rank_reverse(f8)
    rank_mix={}
    for code in code_list:
        rank_mix[code]=r1*rank1[code]+r2*rank2[code]+r3*rank3[code]+r4*rank4[code]+r5*rank5[code]+r6*rank6[code]+r7*rank7[code]+r8*rank8[code]+trade_s[code]+ipo_d[code]
    final_result=sorted(rank_mix.items(),key=lambda d:d[1],reverse=False)
    selecti={};
    for j in range(top):       
        selecti[final_result[j][0]] = j
#    select.append(selecti.keys())
    select.append(list(selecti.keys()))
    
    for code in code_list:
        if code not in code_list_set:
            code_list_set.append(code)

original_data1=w.wsd(code_list_set,'close',w.tdaysoffset(-30,start).Data[0][0],stop,'Fill=Previous;PriceAdj=F')
original_data2=w.wsd(code_list_set,'close',start,stop,'Fill=Previous;PriceAdj=F')
#original_data3=w.wsd(code_list_set, "pct_chg", dayZeroOne , date, "Fill=Previous")
price_info={}
price_series={} 
#pct_chg_series={}           

for code in code_list_set:
    h=code_list_set.index(code)
    price_info[code]=original_data2.Data[h]
    price_series[code]=original_data1.Data[h]


dayZeroOne = w.tdaysoffset(-1, start).Data[0]
DAY = dayZeroOne[0].strftime('%Y%m%d')
cash=8000000.0
value_open=[]
value_close=[]
value_stock_close = []
stock_close=[]      #一天收盘的股票仓位
stock_open=[]       #一天开盘的股票仓位
stock_hand={}       #实时股票仓位变量 
cash_open=[]
cash_close=[]
high_rec = {}
SDREC = []
x=2
new_cash = 0
value = 0
tax_rate = 0.001
delete_all=[]
add_all=[]
high_price=[]
for today in trade:
    stock_hand = {}
    new_cash =[]
    high_rec = {}
    #计算开盘时候的股票仓位，用前一交易日的收盘价结算
    # 开盘的cash和stock
    i=trade.index(today)
    if today in change:
        cash_open.append(0)     
        if i==0:             
            value_open.append(float(cash))
            k = change.index(today)
            per_value = value_open[-1]/float(top)
            for j in range(top):
                CODE = select[k][j]
                PRICE = w.wsd(CODE,'close',DAY,DAY,'Fill=Previous;PriceAdj=F').Data[0][0]
                if PRICE is None:
                    PRICE = 10000
                stock_hand[select[k][j]] = np.int((per_value/PRICE)/100.)*100
            stock_open.append(stock_hand.copy())        #当期股票 
            stock_values = 0
            for m in range(len(stock_hand.keys())):
                stock_values += list(stock_hand.values())[m] * price_info[list(stock_hand.keys())[m]][i]
            new_cash = cash_open[-1] + value_open[0] - stock_values
        else:
            value_open.append(float(value_close[-1]))
            k = change.index(today)
            per_value = value_open[-1]/float(top)
            for j in range(top):
                stock_hand[select[k][j]] = np.int((per_value/price_info[select[k][j]][i-1])/100.)*100
            stock_open.append(stock_hand.copy()) #当期股票 
            stock_values = 0
            for m in range(len(stock_hand.keys())):
                stock_values += list(stock_hand.values())[m] * price_info[list(stock_hand.keys())[m]][i]
            new_cash = cash_open[-1] + float(value_close[-1]) - stock_values
        stock_hold = stock_hand.copy()
    else:
        cash_open.append(float(cash_close[-1]))  ###
        stock_open.append(stock_close[-1].copy())
        stock_hand = stock_open[-1].copy()
        new_cash = cash_open[-1]
        value = []
        value = cash_open[-1]
        for key in stock_hand.keys():
            value += stock_hand[key]*price_info[key][i-1]
        value_open.append(float(value))
        for key in add_list:
            new_cash -= stock_hold[str(key)]*price_info[key][i-1]
            stock_hand[str(key)] = stock_hold[str(key)]
            stock_open[-1][str(key)] = stock_hold[str(key)]
        for key in delete_list:
            new_cash += stock_hand[str(key)]*high_price[-1][str(key)]*(1-x*sd)
            stock_hand.pop(str(key))
            stock_open[-1].pop(str(key))
    if today in change:
        if i != 0:
            for key in delete_list:
                if key in stock_hold.keys(): 
                    new_cash += stock_hold[str(key)]*high_price[-1][str(key)]*(1-x*sd)
                    stock_hand.pop(str(key)) 
                    stock_open[-1].pop(str(key))
    if i!= 0:
        delete_all.append(delete_list)
        add_all.append(add_list)
    else:
        delete_all.append([])
        add_all.append([])
    #日内止损
    delete_list = []
    add_list = []
    #搜索最高价
    for key in stock_hand.keys():
        high = 0
        low = 1000
        j = 0
        while key in stock_open[j-1].keys():    #搜索实际持有记录，取实际持有以来的最高价
            if high < price_info[key][i+j]:
                high = price_info[key][i+j]
            j-=1
            if i+j <= 0:
                break
        # std 根据前20日涨跌幅标准差来确定是否删除股票
        yie = []
        for t in range(i-len(trade)-21,i-len(trade)-1):
            yie.append(np.log(price_series[key][t]/price_series[key][t-1]).copy())
        sd=np.std(yie)
        SDREC.append(sd)
        #判别标准为若今日收盘价小于历史最高价的（1-2*前20日涨跌幅的标准差)，则剔除股票
        if price_info[key][i]/high <= (1-x*sd):
            delete_list.append(str(key))
            high_rec[key] = high
    for key in stock_hold.keys(): 
        if (price_info[key][i]/price_info[key][i-1]-1 >= 0.03) and (key not in stock_hand.keys()):
            add_list.append(str(key))
    high_price.append(high_rec)
    #delete_list = [] 
    #收盘结算
    cash_close.append(float(new_cash))
    stock_close.append(stock_hand.copy())
    value = 0
    if today in change:
        for key in stock_hand:
            value += (stock_hand[key]*price_info[key][i])*(1-tax_rate)
    else:
        for key in stock_hand:
            if key in add_list:
                value += (stock_hand[key]*price_info[key][i])*(1-tax_rate)
            else:
                value += stock_hand[key]*price_info[key][i]
    value_stock_close.append(float(value))
    value += new_cash
    value_close.append(float(value))    


    
index_value=w.wsd(index,'close',start,stop).Data[0]
datelist = [dt.strftime('%Y-%m-%d') for dt in trade]
changelist = [dt.strftime('%Y-%m-%d') for dt in change]

def chg_drawdown(stock_value):
    drawdownlist=[]
    pct_chglist=[]
    for value in stock_value:
        i = stock_value.index(value)
        if i == 0:
            down=0
            chg_amt=0
        else:
            down = abs(value/np.max(pd.Series(stock_value[0:i]))-1)
            chg_amt=stock_value[i]/stock_value[i-1]-1
        drawdownlist.append(down)
        pct_chglist.append(chg_amt)
    return drawdownlist,pct_chglist
    

           
def generate_df(stock_value,index_value,datelist,index,top):
    drawdown,sharp_ratio,std=net_index(stock_value)
    drawdownlist,pct_chglist=chg_drawdown(stock_value)
    returnlist = list(pd.Series(stock_value)/stock_value[0]-1)
    index_return = list(pd.Series(index_value)/index_value[0]-1)
    dataDF=pd.DataFrame({'date':datelist,'return':returnlist,'drawdown':drawdownlist,'pct_chg':pct_chglist,'index_return':index_return,'top':top,'maxdrawdown':drawdown,'sharp_ratio':sharp_ratio,'stdlist':std,'stock_value':stock_value,'index_chg':index_chglist})
    return dataDF  
    
    

index_downlist,index_chglist=chg_drawdown(index_value)    

data1 = generate_df(value_close,index_value,datelist,index,top)  #top80 结果

alo1='5factornew1_PA80_'+str(top)+'_from_'+datelist[0]+'_to_'+datelist[-1]+'.csv'
data1.to_csv(alo1, encoding='gbk')


file1=xlwt.Workbook(encoding='utf-8',style_compression=0)
sheet=file1.add_sheet('stock_hold')

for i in range(len(stock_close)):
    sheet.write(i,0,datelist[i])
    j=1
    for item in stock_close[i].keys():
        sheet.write(i,j,item)
        j=j+1

sheet2=file1.add_sheet('delete_stock')
for i in range(len(delete_all)):
    sheet2.write(i,0,datelist[i])
    j=1
    for item in delete_all[i]:
        sheet2.write(i,j,item)
        j=j+1

sheet3=file1.add_sheet('add_stock')
for i in range(len(add_all)):
    sheet3.write(i,0,datelist[i])
    j=1
    for item in add_all[i]:
        sheet3.write(i,j,item)
        j=j+1
        
#持仓明细的excel文件名
file1.save('5factornew1_stock_delete_add_hold_PA80'+'_from_'+datelist[0]+'_to_'+datelist[-1]+str(top)+'.xls')
